Skip Headers

Oracle® XML Developer's Kit Programmer's Guide 10g Release 2 (10.2) Part Number B14252-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 11 Using the XSQL Pages Publishing Framework

This chapter contains these topics:

- Introduction to the XSQL Pages Publishing Framework

- Using the XSQL Pages Publishing Framework: Overview

- Generating and Transforming XML with XSQL Servlet

- Using XSQL in Java Programs

- XSQL Pages Tips and Techniques

## Introduction to the XSQL Pages Publishing Framework

The Oracle XSQL pages publishing framework is an extensible platform for publishing XML in multiple formats. The Java-based XSQL servlet, which is the center of the framework, provides a declarative interface for dynamically publishing dynamic Web content based on relational data.

The XSQL framework combines the power of SQL, XML, and XSLT. You can use it to create declarative templates called XSQL pages to perform the following actions:

- Assemble dynamic XML datagrams based on parameterized SQL queries

- Transform datagrams with XSLT to generate a result in an XML, HTML, or text-based format

An XSQL page, so called because its default extension is `.xsql`, is an XML file that contains instructions for the XSQL servlet. The Example 11-1 shows a simple XSQL page. It uses the <xsql:query> action element to query the `hr.employees` table.

Example 11-1 Sample XSQL Page

    <?xml version="1.0">
    <?xml-stylesheet type="text/xsl" href="emplist.xsl"?>
    <xsql:query connection="hr" xmlns:xsql="urn:oracle-xsql">
     SELECT * FROM employees
    </xsql:query>

You can present a browser client with the data returned from the query in Example 11-1. Assembling and transforming information for publishing requires no programming. You can perform most tasks in a declarative way. If one of the built-in features does not fit your needs, however, then you can use Java to integrate custom data sources or perform customized server-side processing.

In the XSQL pages framework, the assembly of information to be published is separate from presentation. This architectural feature enables you to do the following:

- Present the same data in multiple ways, including tailoring the presentation appropriately to the type of client device making the request (browser, cellular phone, PDA, and so on)

- Reuse data by aggregating existing pages into new ones

- Revise and enhance the presentation independently of the content

### Prerequisites

This chapter assumes that you are familiar with the following technologies:

- Oracle Database SQL. The XSQL framework accesses data in a database.

- PL/SQL. The XDK supplies a PL/SQL API for XSU that mirrors the Java API.

- Java Database Connectivity (JDBC). The XSQL pages framework depends on a JDBC driver for database connections.

- eXtensible Stylesheet Language Transformation (XSLT). You can use XSLT to transform the data into a format appropriate for delivery to the user.

- XML SQL Utility (XSU). The XSQL pages framework uses XSU to query the database.

## Using the XSQL Pages Publishing Framework: Overview

This section contains the following topics:

- Using the XSQL Pages Framework: Basic Process

- Setting Up the XSQL Pages Framework

- Running the XSQL Pages Demo Programs

- Using the XSQL Pages Command-Line Utility

### Using the XSQL Pages Framework: Basic Process

The XSQL page processor engine interprets, caches, and processes the contents of XSQL pages. Figure 11-1 shows the basic architecture of the XSQL pages publishing framework. The XSQL page processor provides access from the following entry points:

- From the command line or in batch mode with the XSQL command-line utility. The `oracle.xml.xsql.XSQLCommandLine` class is the command-line interface.

- Over the Web by using the XSQL servlet installed in a Web server. The `oracle.xml.xsql.XSQLServlet` class is the servlet interface.

- As part of JSP applications by using `<jsp:include>` to include a template or `<jsp:forward>` to forward a template.

- Programmatically by using the `oracle.xml.xsql.XSQLRequest` Java class.

Figure 11-1 XSQL Pages Framework Architecture

Description of the illustration xsql5.gif

You can run the same XSQL pages from any of the access points shown in Figure 11-1. Regardless of which way you use the XSQL page processor, it performs the following actions to generate a result:

1. Receives a request to process an XSQL page. The request can come from the command line utility or programmatically from an `XSQLRequest` object.

2. Assembles an XML datagram by using the result of one or more SQL queries. The query is specified in the <xsql:query> element of the XSQL page.

3. Returns this XML datagram to the requestor.

4. Optionally transforms the datagram into any XML, HTML, or text-based format.

Figure 11-2 shows a typical Web-based scenario in which a Web server receives an HTTP request for `Page.xsql`, which contains a reference to the XSLT stylesheet `Style.xsl`. The XSQL page contains a database query.

Figure 11-2 Web Access to XSQL Pages

The XSQL page processor shown in Figure 11-2 performs the following steps:

1. Receives a request from the XSQL Servlet to process `Page.xsql`.

2. Parses `Page.xsql` with the Oracle XML Parser and caches it.

3. Connects to the database based on the value of the connection attribute on the document element.

4. Generates the XML datagram by replacing each XSQL action element, for example, <xsql:query>, with the XML results returned by its built-in action handler.

5. Parses the `Style.xsl` stylesheet and caches it.

6. Transforms the datagram by passing it and the `Style.xsl` stylesheet to the Oracle XSLT processor.

7. Returns the resulting XML or HTML document to the requester.

During the transformation step in this process, you can use stylesheets that conform to the W3C XSLT 1.0 or 2.0 standard to transform the assembled datagram into document formats such as the following:

- HTML for browser display

- Wireless Markup Language (WML) for wireless devices

- Scalable Vector Graphics (SVG) for data-driven charts, graphs, and diagrams

- XML Stylesheet Formatting Objects (XSL-FO), for rendering into Adobe PDF

- Text documents such as e-mails, SQL scripts, Java programs, and so on

- Arbitrary XML-based document formats

### Setting Up the XSQL Pages Framework

You can develop and use XSQL pages in various scenarios. This section describes the following topics:

- Creating and Testing XSQL Pages with Oracle JDeveloper

- Setting the CLASSPATH for XSQL Pages

- Configuring the XSQL Servlet Container

- Setting Up the Connection Definitions

#### Creating and Testing XSQL Pages with Oracle JDeveloper

The easiest way to use XSQL pages is with Oracle JDeveloper 10g. The IDE supports the following features:

- Color-coded syntax highlighting

- XML syntax checking

- In-context drop-down lists that help you pick valid XSQL tag names and auto-complete tag and attribute names

- XSQL page deployment and testing

- Debugging tools

- Wizards for creating XSQL actions

To create an XSQL page in an Oracle JDeveloper 10g project, do the following steps:

1. Create or open a project.

2. Select File and then New.

3. In the New Gallery dialog box, select the General category and then XML.

4. In the Item window, select XSQL Page and click OK. JDeveloper loads a tab for the new XSQL page into the central window.

To add XSQL action elements such as <xsql:query> to your XSQL page, place the cursor where you want the new element to go and click an item in the Component Palette. A wizard opens that takes you through the steps of selecting which XSQL action you want to use and which attributes you need to provide.

To check the syntax of an XSQL page, place the cursor in the page and right-click Check XML Syntax. If there are any XML syntax errors, JDeveloper displays them.

To test an XSQL page, select the page in the navigator and right-click Run. JDeveloper automatically starts up a local Web server, properly configured to run XSQL pages, and tests your page by launching your default browser with the appropriate URL to request the page. After you have run the XSQL page, you can continue to make modifications to it in the IDE as well as to any XSLT stylesheets with which it might be associated. After saving the files in the IDE you can immediately refresh the browser to observe the effect of the changes.

You must add the XSQL runtime library to your project library list so that the `CLASSPATH` is properly set. The IDE adds this entry automatically when you go through the New Gallery dialog to create a new XSQL page, but you can also add it manually to the project as follows:

1. Right-click the project in the Applications Navigator.

2. Select Project Properties.

3. Select Profiles and then Libraries from the navigation tree.

4. Move XSQL Runtime from the Available Libraries pane to Selected Libraries.

#### Setting the CLASSPATH for XSQL Pages

Outside of the JDeveloper environment, you should make sure that the XSQL page processor engine is properly configured.

Make sure that the appropriate JAR files are in the `CLASSPATH` of the JavaVM that processes the XSQL Pages. The complete set of XDK JAR files is described in Table 2-1, "Java Libraries for XDK Components". The JAR files for the XSQL framework include the following:

- `xml.jar`, the XSQL page processor

- `xmlparserv2.jar`, the Oracle XML parser

- `xsu12.jar`, the Oracle XML SQL utility (XSU)

- `classes12.jar`, the Oracle JDBC driver

Note: The XSQL servlet can connect to any database that has JDBC support. Indicate the appropriate JDBC driver class and connection URL in the XSQL configuration file connection definition. Object-relational functionality only works when using Oracle database with the Oracle JDBC driver.

If you have configured your `CLASSPATH` as instructed in "Setting Up the Java XDK Environment", then you only need to add the directory where the XSQL pages configuration file resides. In the database installation of the XDK, the directory for `XSQLConfig.xml` is `$ORACLE_HOME/xdk/admin`.

On Windows your `%CLASSPATH%` variable should contain the following entries:

    %ORACLE_HOME%\lib\classes12.jar;%ORACLE_HOME%\lib\xmlparserv2.jar;
    %ORACLE_HOME%\lib\xsu12.jar;C:\xsql\lib\xml.jar;%ORACLE_HOME%\xdk\admin

On UNIX the `$CLASSPATH` variable should contain the following entries:

    $ORACLE_HOME/lib/classes12.jar:$ORACLE_HOME/lib/xmlparserv2.jar:
    $ORACLE_HOME/lib/xsu12.jar:$ORACLE_HOME/lib/xml.jar:$ORACLE_HOME\xdk\admin

Note: If you are deploying your XSQL pages in a J2EE WAR file, then you can include the XSQL JAR files in the `./WEB-INF/lib` directory of the WAR file.

#### Configuring the XSQL Servlet Container

You can install the XSQL servlet in a variety of different Web servers, including OC4J, Jakarta Tomcat, and so forth. You can find complete instructions for installing the servlet in the Release Notes for the OTN download of the XDK.

Navigate to the setup instructions as follows:

1. Log on to OTN and navigate to the following URL:

       http://www.oracle.com/technology/tech/xml/xdk/doc/production10g/readme.html

2. Click Getting Started with XDK Java Components.

3. In the Introduction section, scroll down to XSQL Servlet in the bulleted list and click Release Notes.

4. In the Contents section, click Downloading and Installing the XSQL Servlet.

5. Scroll down to the Setting Up Your Servlet Engine to Run XSQL Pages section and look for your Web server.

#### Setting Up the Connection Definitions

XSQL pages specify database connections by using a short name for a connection that is defined in the XSQL configuration file, which by default is named `$ORACLE_HOME/xdk/admin/XSQLConfig.xml`.

Note: If you are deploying your XSQL pages in a J2EE WAR file, then you can place the `XSQLConfig.xml` file in the `./WEB-INF/classes` directory of your WAR file.

The sample XSQL page shown in Example 11-1 contains the following connection information:

    <xsql:query connection="hr" xmlns:xsql="urn:oracle-xsql">

Connection names are defined in the `<connectiondefs>` section of the XSQL configuration file. Example 11-2 shows the relevant section of the sample configuration file included with the database, with the `hr` connection in bold.

Example 11-2 Connection Definitions Section of XSQLConfig.xml

    <connectiondefs>
      ...
      <connection name="hr">
        <username>hr</username>
        <password>hr</password>
        <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl>
        <driver>oracle.jdbc.driver.OracleDriver</driver>
        <autocommit>false</autocommit>
      </connection>
      ...
    </connectiondefs>

For each database connection, you can specify the following elements:

- `<username>`, the database username

- `<password>`, the database password

- `<dburl>`, the JDBC connection string

- `<driver>`, the fully-qualified class name of the JDBC driver to use

- `<autocommit>`, which optionally forces `AUTOCOMMIT` to `TRUE` or `FALSE`

Specify an `<autocommit>` child element to control the setting of the JDBC autocommit for any connection. If no `<autocommit>` child element is set for a `<connection>`, then the autocommit setting is not set by the XSQL connection manager. In this case, the setting is the default autocommit setting for the JDBC driver.

You can place an arbitrary number of `<connection>` elements in the XSQL configuration file to define your database connections. An individual XSQL page refers to the connection it wants to use by putting a `connection="``xxx``"` attribute on the top-level element in the page (also called the "document element").

Caution: The `XSQLConfig.xml` file contains sensitive database username and password information that you want to keep secure on the database server. Refer to "Security Considerations for XSQL Pages" for instructions.

### Running the XSQL Pages Demo Programs

Demo programs for the XSQL servlet are included in `$ORACLE_HOME/xdk/demo/java/xsql`. Table 11-1 lists the demo subdirectories and explains the included demos. The Demo Name column refers to the title of the demo listed on the XSQL Pages & XSQL Servlet home page. "Running the XSQL Demos" explains how to access the home page.

Table 11-1 XSQL Servlet Demos

Directory | Demo Name | Description
--- | --- | ---
`home/` | XSQL Pages & XSQL Servlet | Contains the pages that display the tabbed home page of the XSQL demos as well as the online XSQL help that you can access from that page. As explained in "Running the XSQL Demos", you can invoke the XSQL home page from the `index.html` page.
`helloworld/` | Hello World Page | Illustrates the simplest possible XSQL page.
`emp/` | Employee Page | XSQL page showing XML data from the `hr.employees` table, using XSQL page parameters to control what employees are returned and which columns to use for the database sort. Uses an associated XSLT Stylesheet to format the results as an HTML Form containing the `emp.xsql` page as the form action so the user can refine the search criteria.
`insclaim/` | Insurance Claim Page | Demonstrates a number of sample queries over the richly-structured Insurance Claim object view. The `insclaim.sql` scripts sets up the `INSURANCE_CLAIM_VIEW` object view and populates it with sample data.
`classerr/` | Invalid Classes Page | Uses `invalidclasses.xsl` to format a "live" list of current Java class compilation errors in your schema. The accompanying SQL script sets up the `XSQLJavaClassesView` object view used by the demo. The master/detail information from the object view is formatted into HTML by the `invalidclasses.xsl` stylesheet in the server.
`doyouxml/` | Do You XML? Site | Shows how a simple, data-driven Web site can be built with an XSQL page that makes use of SQL, XSQL substitution variables in the queries, and XSLT for formatting the site. Demonstrates using substitution parameters in both the body of SQL query statements within <xsql:query> tags, as well as within the attributes to `<xsql:query>` tags to control behavior such as how many records to display and to skip (for "paging" through query results in a stateless way).
`empdept/` | Emp/Dept Object Demo | Demonstrates how to use an object view to group master/detail information from two existing flat tables such as `scott.emp` and `scott.dept`. The `empdeptobjs.sql` script creates the object view as well as `INSTEAD OF INSERT` triggers that enable the master/detail view to be used as an insert target of `xsql:insert-request`. The `empdept.xsl` stylesheet illustrates a form of an XSLT stylesheet that looks just like an HTML page without the extra `xsl:stylesheet` or `xsl:transform` at the top. Using a Literal Result Element as Stylesheet is part of the XSLT 1.0 specification. The stylesheet also shows how to generate an HTML page that includes `<link rel="stylesheet">` to enable the generated HTML to fully leverage CSS for centralized HTML style information, found in the `coolcolors.css` file.
`airport/` | Airport Code Validation | Returns a datagram of information about airports based on their three-letter codes and uses `<xsql:no-rows-query>` as alternative queries when initial queries return no rows. After attempting to match the airport code passed in, the XSQL page tries a fuzzy match based on the airport description. The `airport.htm` page shows how to use the XML results of the `airport.xsql` page from a Web page with JavaScript to exploit built-in DOM functionality in Internet Explorer. When you enter the three-letter airport code on the Web page, a JavaScript fetches an XML datagram from XSQL servlet. The datagram corresponds to the code that you entered. If the return indicates no match, then the program collects a "picklist" of possible matches based on information returned in the XML datagram from XSQL servlet
`airport/` | Airport Code Display | Demonstrates use of the same XSQL page as the Airport Code Validation example but supplies an XSLT stylesheet name in the request. This behavior causes the airport information to be formatted as an HTML form instead of being returned as raw XML.
`airport/` | Airport Soap Service | Demonstrates returning airport information as a SOAP Service.
`adhocsql/` | Adhoc Query Visualization | Demonstrates how to pass a SQL query and XSLT stylesheet as parameters to the server.
`document/` | XML Document Demo | Demonstrates inserting XML documents into relational tables. The `docdemo.sql` script creates a user-defined type called XMLDOCFRAG containing an attribute of type CLOB. Try inserting the text of the document in `./xsql/demo/xml99.xml` and providing the name `xml99.xsl` as the stylesheet, as well as `./xsql/demo/JDevRelNotes.xml` with the stylesheet `relnotes.xsl`. The `docstyle.xsql` page illustrates an example of the <xsql:include-xsql> action element to include the output of the `doc.xsql` page into its own page before transforming the final output using a client-supplied stylesheet name. The demo uses the client-side XML features of Internet Explorer 5.0 to check the document for well-formedness before allowing it to be posted to the server.
`insertxml/` | XML Insert Request Demo | Demonstrates posting XML from a client to an XSQL page that handles inserting the posted XML data into a database table using the <xsql:insert-request> action element. The demo accepts XML documents in the `moreover.com` XML-based news format. In this case, the program doing the posting of the XML is a client-side Web page using Internet Explorer 5.0 and the XMLHttpRequest object from JavaScript. If you look at the source for the `insertnewsstory.xsql` page, you'll see it's specifying a table name and an XSLT Transform name. The `moreover-to-newsstory.xsl` stylesheet transforms the incoming XML information into the canonical format that the OracleXMLSave utility knows how to insert. Try copying and pasting the example `<article>` element several times within the `<moreovernews>` element to insert several new articles in one shot. The `newsstory.sql` script shows how INSTEAD OF triggers can be used on the database views into which you ask XSQL Pages to insert to the data to customize how incoming data is handled, default primary key values, and so on.
`svg/` | Scalable Vector Graphics Demo | The `deptlist.xsql` page displays a simple list of departments with hyperlinks to the `SalChart.xsql` page. The `SalChart.xsql` page queries employees for a given department passed in as a parameter and uses the associated `SalChart.xsql` stylesheet to format the result into a Scalable Vector Graphics drawing, a bar chart comparing salaries of the employees in that department.
`fop/` | PDF Demo | The `emptable.xsql` page displays a simple list of employees. The `emptable.xsl` stylesheet transforms the datapage into the XSL-FO Formatting Objects which, combined with the built-in FOP serializer, render the results in Adobe PDF format.
`cursor/` | Cursor Demo | Contains an example of using a nested `CURSOR` expression, which is one of three ways to use the default <xsql:query> element to produce nested elements.
`actions/` |  | Contains the source code for two example custom actions.

#### Setting Up the XSQL Demos

To set up the XSQL demos perform the following steps:

1. Change into the `$ORACLE_HOME/xdk/demo/java/xsql` directory (UNIX) or `%ORACLE_HOME%\xdk\demo\java\xsql` directory (Windows).

2. Start SQL*Plus and connect to your database as `ctxsys` — the schema owner for the Oracle Text packages — and issue the following statement:

       GRANT EXECUTE ON ctx_ddl TO scott;

3. Connect to your database as a user with `DBA` privileges and issue the following statement:

       GRANT QUERY REWRITE TO scott;

   The preceding query enables `scott` to create a function-based index that one of the demos requires to perform case-insensitive queries on descriptions of airports.

4. Connect to your database as `scott/tiger`.

5. Run the SQL script `install.sql` in the current directory. This script runs all SQL scripts for all the demos:

       @install.sql

6. Change to the `./doyouxml` subdirectory, and run the following command to import sample data for the "Do You XML?" demo:

       imp scott/tiger file=doyouxml.dmp

7. To run the Scalable Vector Graphics (SVG) demonstration, install an SVG plug-in such as Adobe SVG plug-in into your browser.

#### Running the XSQL Demos

The XSQL demos are designed to be accessed through a Web browser. If you have set up the XSQL servlet in a Web server as described in "Configuring the XSQL Servlet Container", then you can access the demos through the following URL, substituting appropriate values for `yourserver` and `port`:

    http://yourserver:port/xsql/index.html

Figure 11-3 shows a section of the XSQL home page in Internet Explorer. Note that you must use browser version 5 or higher.

Figure 11-3 XSQL Home Page

The demos are designed to be self-explanatory. Click the demo titles—Hello World Page, Employee Page, and so forth—and follow the online instructions.

### Using the XSQL Pages Command-Line Utility

Often the content of a dynamic page is based on data that does not frequently change. To optimize performance of your Web publishing, you can use operating system facilities to schedule offline processing of your XSQL pages. This technique enables the processed results to be served statically by your Web server.

The XDK includes a command-line Java interface that runs the XSQL page processor. You can process any XSQL page with the XSQL command-line utility.

The `$ORACLE_HOME/xdk/bin/xsql` and `%ORACLE_HOME%\xdk\bin\xsql.bat` shell scripts run the `oracle.xml.xsql.XSQLCommandLine` class. Before invoking the class make sure that your environment is configured as described in "Setting Up the XSQL Pages Framework". Depending on how you invoke the utility, the syntax is either of the following:

    java oracle.xml.xsql.XSQLCommandLine xsqlpage [outfile] [param1=value1 ...]
    xsql xsqlpage [outfile] [param1=value1 ...]

If you specify an `outfile`, then the result of processing `xsqlpage` is written to it; otherwise the result goes to standard out. You can pass any number of parameters to the XSQL page processor, which are available for reference by the XSQL page processed as part of the request. However, the following parameter names are recognized by the command-line utility and have a pre-defined behavior:

- `xml-stylesheet=stylesheetURL`

  Provides the relative or absolute URL for a stylesheet to use for the request. You can also set it to the string `none` to suppress XSLT stylesheet processing for debugging purposes.

- `posted-xml=XMLDocumentURL`

  Provides the relative or absolute URL of an XML resource to treat as if it were posted as part of the request.

- `useragent=UserAgentString`

  Simulates a particular HTTP User-Agent string from the command line so that an appropriate stylesheet for that User-Agent type is selected as part of command-line processing of the page.

## Generating and Transforming XML with XSQL Servlet

This section describes the most basic tasks that you can perform with your server-side XSQL page templates:

- Composing XSQL Pages

- Producing Datagrams from SQL Queries

- Transforming XML Datagrams into an Alternative XML Format

- Transforming XML Datagrams into HTML for Display

### Composing XSQL Pages

You can serve database information in XML format over the Web with XSQL pages. For example, suppose your aim is to serve a real-time XML datagram from Oracle of all available flights landing today at JFK airport. Example 11-3 shows a sample XSQL page in a file named `AvailableFlightsToday.xsql`.

Example 11-3 AvailableFlightsToday.xsql

    <?xml version="1.0"?>
    <xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">
      SELECT    Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') AS Due
      FROM      FlightSchedule
      WHERE     TRUNC(ExpectedTime) = TRUNC(SYSDATE)
      AND       Arrived = 'N'
      AND       Destination = ?   /* The "?" represents a bind variable bound */
      ORDER BY  ExpectedTime      /* to the value of the City parameter.      */
    </xsql:query>

The XSQL page is an XML file that contains any mix of static XML content and XSQL action elements. The file can have any extension, but `.xsql` is the default extension for XSQL pages. You can modify your servlet engine configuration settings to associate other extensions by using the same technique described in "Configuring the XSQL Servlet Container". Note that the servlet extension mapping is configured inside the `./WEB-INF/web.xml` file in a J2EE WAR file.

The XSQL page in Example 11-3 begins with the following declaration:

    <?xml version="1.0"?>

The first, outermost element in an XSQL page is the document element. `AvailableFlightsToday.xsql` contains a single XSQL action element <xsql:query>, but no static XML elements. In this case the `<xsql:query>` element is the document element. Example 11-3 represents the simplest useful XSQL page: one that contains a single query. The results of the query replace the `<xsql:query>` section in the XSQL page.

Note: Chapter 27, "XSQL Pages Reference" describes the complete set of built-in action elements.

The `<xsql:query>` action element includes an `xmlns` attribute that declares the `xsql` namespace prefix as a synonym for the `urn:oracle-xsql` value, which is the Oracle XSQL namespace identifier:

    <xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">

The element also contains a `connection` attribute whose value is the name of one of the pre-defined connections in the XSQL configuration file:

    <xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">

The details concerning the username, password, database, and JDBC driver that will be used for the `demo` connection are centralized in the configuration file.

To include more than one query on the page, you can invent an XML element to wrap the other elements. Example 11-4 illustrates this technique.

Example 11-4 Wrapping the <xsql:query> Element

    <?xml version="1.0"?>
    <page connection="demo" xmlns:xsql="urn:oracle-xsql">
      <xsql:query bind-params="City">
        SELECT   Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') AS Due
        FROM     FlightSchedule
        WHERE    TRUNC(ExpectedTime) = TRUNC(SYSDATE)
        AND      Arrived = 'N'
        AND      Destination = ?   /* The ? is a bind variable bound       */
        ORDER BY ExpectedTime      /* to the value of the City parameter.  */
      </xsql:query>
      <!-- Other xsql:query actions can go here inside <page> and </page> -->
    </page>

In Example 11-4, the `connection` attribute and the `xsql` namespace declaration always go on the document element, whereas the `bind-params` is specific to the `<xsql:query>` action.

#### Using Bind Parameters

The `<xsql:query>` element shown in Example 11-3 contains a `bind-params` attribute that associates the values of parameters in the request to bind variables in the SQL statement included in the `<xsql:query>` tag. The bind parameters in the SQL statement are represented by question marks.

You can use SQL bind variables to parameterize the results of any of the actions in Table 27-1, "Built-In XSQL Elements and Action Handler Classes" that allow SQL statements. Bind variables enable your XSQL page template to produce results based on the values of parameters passed in the request.

To use a bind variable, include a question mark anywhere in a statement where bind variables are allowed by SQL. Whenever a SQL statement is executed in the page, the XSQL engine binds the parameter values to the variable by specifying the `bind-params` attribute on the action element.

Example 11-5 illustrates an XSQL page that binds the bind variables to the value of the `custid` parameter in the page request.

Example 11-5 CustomerPortfolio.xsql

    <portfolio connnection="prod" xmlns:xsql="urn:oracle-xsql">
      <xsql:query bind-params="custid">
        SELECT s.ticker as "Symbol", s.last_traded_price as "Price"
        FROM latest_stocks s, customer_portfolio p
        WHERE p.customer_id = ?
        AND s.ticker = p.ticker
      </xsql:query>
    </portfolio>

The XML data for a customer with ID of 101 can then be requested by passing the customer id parameter in the request as follows:

    http://yourserver.com/fin/CustomerPortfolio.xsql?custid=1001

The value of the `bind-params` attribute is a space-delimited list of parameter names. The left-to-right order indicates the positional bind variable to which its value will be bound in the statement. Thus, if your SQL statement contains five question marks, then the `bind-params` attribute needs a space-delimited list of five parameter names. If the same parameter value needs to be bound to several different occurrences of a bind variable, then repeat the name of the parameters in the value of the `bind-params` attribute at the appropriate position. Failure to include the same number of parameter names in the `bind-params` attribute as in the query results in an error when the page is executed.

You can use variables in any action that expects a SQL statement or PL/SQL block. The page shown in Example 11-6 illustrates this technique. The XSQL page contains three action elements:

- <xsql:dml> binds `useridCookie` to an argument in the `log_user_hit` procedure.

- <xsql:query> binds parameter `custid` to a variable in a `WHERE` clause.

- <xsql:include-owa> binds parameters `custid` and `userCookie` to two arguments in the `historical_data` procedure.

Example 11-6 CustomerPortfolio.xsql

    <portfolio connnection="prod" xmlns:xsql="urn:oracle-xsql">
      <xsql:dml commit="yes" bind-params="useridCookie">
         BEGIN log_user_hit(?); END;
      </xsql:dml>
      <current-prices>
        <xsql:query bind-params="custid">
          SELECT s.ticker as "Symbol", s.last_traded_price as "Price"
          FROM latest_stocks s, customer_portfolio p
          WHERE p.customer_id = ?
          AND s.ticker = p.ticker
        </xsql:query>
      </current-prices>
      <analysis>
        <xsql:include-owa bind-params="custid userCookie">
          BEGIN portfolio_analysis.historical_data(?,5 /* years */, ?); END;
        </xsql:include-owa>
      </analysis>
    </portfolio>

#### Using Lexical Substitution Parameters

For any XSQL action element, you can substitute the value of any attribute or the text of any contained SQL statement by means of a lexical substitution parameter. Thus, you can parameterize how actions behave as well as substitute parts of the SQL statements that they perform. Lexical substitution parameters are referenced with the following syntax: {`@ParameterName`}.

Example 11-7 illustrates how you can use two lexical substitution parameters. One parameter in the <xsql:query> element sets the maximum number of rows to be passed in, whereas the other controls the list of columns to be ordered.

Example 11-7 DevOpenBugs.xsql

    <!-- DevOpenBugs.xsql -->
    <open-bugs connection="demo" xmlns:xsql="urn:oracle-xsql">
      <xsql:query max-rows="{@max}" bind-params="dev prod">
        SELECT bugno, abstract, status
        FROM   bug_table
        WHERE  programmer_assigned = UPPER(?)
        AND    product_id          = ?
        AND    status < 80
        ORDER BY {@orderby}
      </xsql:query>
    </open-bugs>

Example 11-7 also contains two bind parameters: `dev` and `prod`. Suppose that you want to obtain the open bugs for developer `smuench` against product `817`. You want to retrieve only 10 rows and order them by bug number. You can fetch the XML for the bug list by specifying parameter values as follows:

    http://server.com/bug/DevOpenBugs.xsql?dev=smuench&prod=817&max=10&orderby=bugno

You can also use the XSQL command-line utility to make the request as follows:

    xsql DevOpenBugs.xsql dev=smuench prod=817 max=10 orderby=bugno

Lexical parameters also enable you to parameterize the XSQL pages connection and the stylesheet used to process the page. Example 11-8 illustrates this technique. You can switch between stylesheets `test.xsql` and `prod.xsl` by specifying the name/value pairs `sheet=test` and `sheet=prod`.

Example 11-8 DevOpenBugs.xsql

    <?xml version="1.0"?>
    <?xml-stylesheet type="text/xsl" href="{@sheet}.xsl"?>
    <!-- DevOpenBugs.xsql -->
    <open-bugs connection="{@conn}" xmlns:xsql="urn:oracle-xsql">
      <xsql:query max-rows="{@max}" bind-params="dev prod">
        SELECT bugno, abstract, status
          FROM bug_table
         WHERE programmer_assigned = UPPER(?)
           AND product_id          = ?
           AND status < 80
        ORDER BY {@orderby}
      </xsql:query>
    </open-bugs>

#### Providing Default Values for Bind and Substitution Parameters

You may want to provide a default value for a bind variable or a substitution parameter directly in the page. In this way, the page is parameterized without requiring the requester to explicitly pass in all values in every request.

To include a default value for a parameter, add an XML attribute of the same name as the parameter to the action element or to any ancestor element. If a value for a given parameter is not included in the request, then the XSQL page processor searches for an attribute by the same name on the current action element. If it does not find one, it keeps looking for such an attribute on each ancestor element of the current action element until it gets to the document element of the page.

The page in Example 11-9 defaults the value of the `max` parameter to `10` for both `<xsql:query>` actions in the page.

Example 11-9 Setting a Default Value

    <example max="10" connection="demo" xmlns:xsql="urn:oracle-xsql">
      <xsql:query max-rows="{@max}">SELECT * FROM TABLE1</xsql:query>
      <xsql:query max-rows="{@max}">SELECT * FROM TABLE2</xsql:query>
    </example>

This page in Example 11-10 defaults the first query to a `max` of `5`, the second query to a `max` of `7`, and the third query to a `max` of `10`.

Example 11-10 Setting Multiple Default Values

    <example max="10" connection="demo" xmlns:xsql="urn:oracle-xsql">
      <xsql:query max="5" max-rows="{@max}">SELECT * FROM TABLE1</xsql:query>
      <xsql:query max="7" max-rows="{@max}">SELECT * FROM TABLE2</xsql:query>
      <xsql:query max-rows="{@max}">SELECT * FROM TABLE3</xsql:query>
    </example>

All defaults are overridden if a value of `max` is supplied in the request, as shown in the following example:

    http://yourserver.com/example.xsql?max=3

Bind variables respect the same defaulting rules. Example 11-11 illustrates how you can set the `val` parameter to `10` by default.

Example 11-11 Defaults for Bind Variables

    <example val="10" connection="demo" xmlns:xsql="urn:oracle-xsql">
      <xsql:query tag-case="lower" bind-params="val val val">
        SELECT ? AS somevalue
        FROM DUAL
        WHERE ? = ?
      </xsql:query>
    </example>

If the page in Example 11-11 is requested without any parameters, it returns the following XML datagram:

    <example>
      <rowset>
        <row>
          <somevalue>10</somevalue>
        </row>
      </row>
    </example>

Alternatively, assume that the page is requested with the following URL:

    http://yourserver.com/example.xsql?val=3

The preceding URL returns the following datagram:

    <example>
      <rowset>
        <row>
          <somevalue>3</somevalue>
        </row>
      </row>
    </example>

You can remove the default value for the `val` parameter from the page by removing the `val` attribute. Example 11-12 illustrates this technique.

Example 11-12 Bind Variables with No Defaults

    <example connection="demo" xmlns:xsql="urn:oracle-xsql">
      <xsql:query tag-case="lower" bind-params="val val val">
        SELECT ? AS somevalue
        FROM DUAL
        WHERE ? = ?
      </xsql:query>
    </example>

A URL request for the page that does not supply a name/value pair returns the following datagram:

    <example>
      <rowset/>
    </example>

A bind variable that is bound to a parameter with neither a default value nor a value supplied in the request is bound to NULL, which causes the `WHERE` clause in Example 11-12 to return no rows.

#### How the XSQL Page Processor Handles Different Types of Parameters

XSQL pages can make use of parameters supplied in the request as well as page-private parameters. The names and values of page-private parameters are determined by actions in the page. If an action encounters a reference to a parameter named `param` in either a `bind-params` attribute or in a lexical parameter reference, then the value of the `param` parameter is resolved in the following order:

1. The value of the page-private parameter named `param`, if set

2. The value of the request parameter named `param`, if supplied

3. The default value provided by an attribute named `param` on the current action element or one of its ancestor elements

4. The value NULL for bind variables and the empty string for lexical parameters

For XSQL pages that are processed by the XSQL servlet over HTTP, you can also set and reference the HTTP-Session-level variables and HTTP Cookies parameters.

For XSQL pages processed through the XSQL servlet, the value of a parameter `param` is resolved in the following order:

1. The value of the page-private parameter `param`, if set

2. The value of the cookie named `param`, if set

3. The value of the session variable named `param`, if set

4. The value of the request parameter named `param`, if supplied

5. The default value provided by an attribute named `param` on the current action element or one of its ancestor elements

6. The value NULL for bind variables and the empty string for lexical parameters

The resolution order means that users cannot supply parameter values in a request to override parameters of the same name set in the HTTP session. Also, users cannot set them as cookies that persist across browser sessions.

### Producing Datagrams from SQL Queries

With XSQL servlet properly installed on your Web server, you can access XSQL pages by following these basic steps:

1. Copy an XSQL file to a directory under the virtual hierarchy of your Web server. Example 11-3 shows the sample page `AvailableFlightsToday.xsql`.

   You can also deploy XSQL pages in a standard J2EE WAR file, which occurs when you use Oracle JDeveloper 10g to develop and deploy your pages to Oracle Application Server.

2. Load the page in your browser. For example, if the root URL is `yourcompany.com`, then you can access the `AvailableFlightsToday.xsql` page through a Web browser by requesting the following URL:

       http://yourcompany.com/AvailableFlightsToday.xsql?City=JFK

The XSQL page processor automatically materializes the results of the query in your XSQL page as XML and returns them to the requester. Typically, another server program requests this XML-based datagram for processing, but if you use a browser such as Internet Explorer, then you can directly view the XML result as shown in Figure 11-4.

Figure 11-4 XML Result From XSQL Page (AvailableFlightsToday.xsql) Query

Description of the illustration xsql1.gif

### Transforming XML Datagrams into an Alternative XML Format

If the canonical `<ROWSET>` and `<ROW>` XML output from Figure 11-4 is not the XML format you need, then you can associate an XSLT stylesheet with your XSQL page. The stylesheet can transform the XML datagram in the server before returning the data.

When exchanging data with another program, you typically agree on a DTD that describes the XML format for the exchange. Assume that you are given the `flight-list.dtd` definition and are told to produce your list of arriving flights in a format compliant with the DTD. You can use a visual tool such as XML Authority to browse the structure of the flight-list DTD, as shown in Figure 11-5.

Figure 11-5 Exploring flight-list.dtd with XML Authority

Description of the illustration xsql2.gif

Figure 11-5 shows that the standard XML formats for flight lists are as follows:

- `<flight-list>` element, which contains one or more `<flight>` elements

- `<flight>` elements, which have attributes `airline` and `number`, and each of which contains an `<arrives>` element

- `<arrives>` elements, which contains text

Example 11-13 shows the XSLT stylesheet `flight-list.xsl`. By associating the stylesheet with the XSQL page, you can change the default `<ROWSET>` and `<ROW>` format into the industry-standard `<flight-list>` and `<flight>`.

Example 11-13 flight-list.xsl

    <!-- XSLT Stylesheet to transform ROWSET/ROW results into flight-list format
     -->
    <flight-list xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                   xsl:version="1.0">
      <xsl:for-each select="ROWSET/ROW">
          <flight airline="{CARRIER}" number="{FLIGHTNUMBER}">
            <arrives><xsl:value-of select="DUE"/></arrives>
          </flight>
      </xsl:for-each>
    </flight-list>

The XSLT stylesheet is a template that includes the literal elements that you want to produce in the resulting document, such as `<flight-list>`, `<flight>`, and `<arrives>`, interspersed with XSLT actions that enable you to do the following:

- Loop over matching elements in the source document with `<xsl:for-each>`

- Plug in the values of source document elements where necessary with `<xsl:value-of>`

- Plug in the values of source document elements into attribute values with the `{some_parameter}` notation

The following items have been added to the top-level `<flight-list>` element in the Example 11-13 stylesheet:

- `xmlns:xsl="http://www.w3.org/1999/XSL/Transform"`

  This attribute defines the XML Namespace named `xsl` and identifies the URL string that uniquely identifies the XSLT specification. Although it looks just like a URL, think of the string `http://www.w3.org/1999/XSL/Transform` as the "global primary key" for the set of elements defined in the XSLT 1.0 specification. When the namespace is defined, you can use the `<xsl:XXX>` action elements in the stylesheet to loop and plug values in where necessary.

- `xsl:version="1.0"`

  This attribute identifies the document as an XSLT 1.0 stylesheet. A version attribute is required on all XSLT stylesheets for them to be valid and recognized by an XSLT processor.

You can associate the `flight-list.xsl` stylesheet with the `AvailableFlightsToday.xsql` in Example 11-3 by adding an `<?xml-stylesheet?>` instruction to the top of the page. Example 11-14 illustrates this technique.

Example 11-14 flight-list.xsl

    <?xml version="1.0"?>
    <?xml-stylesheet type="text/xsl" href="flight-list.xsl"?>
    <xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">
        SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') AS Due
          FROM FlightSchedule
         WHERE TRUNC(ExpectedTime) = TRUNC(SYSDATE) AND Arrived = 'N'
           AND Destination = ?   /* The ? is a bind variable being bound */
          ORDER BY ExpectedTime  /* to the value of the City parameter   */
    </xsql:query>

Associating an XSLT stylesheet with the XSQL page causes the requesting program or browser to view the XML in the format as specified by `flight-list.dtd` you were given. Figure 11-6 illustrates a sample browser display.

Figure 11-6 XSQL Page Results in XML Format

Description of the illustration xsql3.gif

### Transforming XML Datagrams into HTML for Display

To return the same XML data in HTML instead of an alternative XML format, use a different XSLT stylesheet. For example, rather than producing elements such as `<flight-list>` and `<flight>`, you can write a stylesheet that produces HTML elements such as `<table>`, `<tr>`, and `<td>`. The result of the dynamically queried data then looks like the HTML page shown in Figure 11-7. Instead of returning raw XML data, the XSQL page leverages server-side XSLT transformation to format the information as HTML for delivery to the browser.

Figure 11-7 Using an XSLT Stylesheet to Render HTML

Description of the illustration xsql4.gif

Similar to the syntax of the `flight-list.xsl` stylesheet, the `flight-display.xsl` stylesheet shown in Example 11-15 looks like a template HTML page. It contains `<xsl:for-each>`, `<xsl:value-of>`, and attribute value templates such as `{DUE}` to plug in the dynamic values from the underlying `<ROWSET>` and `<ROW>` structured XML query results.

Example 11-15 flight-display.xsl

    <!-- XSLT Stylesheet to transform ROWSET/ROW results into HTML -->
    <html xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xsl:version="1.0">
      <head><link rel="stylesheet" type="text/css" href="flights.css" /></head>
      <body>
        <center><table border="0">
          <tr><th>Flight</th><th>Arrives</th></tr>
          <xsl:for-each select="ROWSET/ROW">
            <tr>
              <td>
                <table border="0" cellspacing="0" cellpadding="4">
                  <tr>
                    <td><img align="absmiddle" src="images/{CARRIER}.gif"/></td>
                    <td width="180">
                      <xsl:value-of select="CARRIER"/>
                      <xsl:text> </xsl:text>
                      <xsl:value-of select="FLIGHTNUMBER"/>
                    </td>
                  </tr>
                </table>
              </td>
              <td align="center"><xsl:value-of select="DUE"/></td>
            </tr>
          </xsl:for-each>
        </table></center>
      </body>
    </html>

Note: The stylesheet produces well-formed HTML. Each opening tag is properly closed (for example, `<td>`…`</td>`); empty tags use the XML empty element syntax `<br/>` instead of just `<br>`.

You can achieve useful results quickly by combining the power of the following:

- Parameterized SQL statements to select information from the Oracle database

- Industry-standard XML as a portable, interim data exchange format

- XSLT to transform XML-based datagrams into any XML- or HTML-based format

## Using XSQL in Java Programs

The `oracle.xml.xsql.XSQLRequest` class enables you to use the XSQL page processor in your Java programs. To use the XSQL Java API, follow these basic steps:

1. Construct an instance of `XSQLRequest`, passing the XSQL page to be processed into the constructor as one of the following:

   - `String` containing a URL to the page

   - `URL` object for the page

   - In-memory `XMLDocument`

2. Invoke one of the following methods on the object to process the page:

   - `process()` to write the result to a `PrintWriter` or `OutputStream`

   - `processToXML()` to return the result as an XML Document

If you want to use the built-in XSQL connection manager, which implements JDBC connection pooling based on XSQL configuration file definitions, then the XSQL page is all you need to pass to the constructor. Optionally, you can pass in a custom implementation for the `XSQLConnectionManagerFactory` interface as well.

The ability to pass the XSQL page as an in-memory `XMLDocument` object means that you can dynamically generate any valid XSQL page for processing. You can then pass the page to the XSQL engine for evaluation.

When processing a page, you may want to perform the following additional tasks as part of the request:

- Pass a set of parameters to the request.

  You accomplish this aim by passing any object that implements the `Dictionary` interface to the `process()` or `processToXML()` methods. Passing a `HashTable` containing the parameters is one popular approach.

- Set an XML document to be processed by the page as if it were the "posted XML" message body.

  You can do this by using the `XSQLResquest.setPostedDocument()` method.

Example 11-16 shows how you can process a page by using `XSQLRequest`.

Example 11-16 XSQLRequestSample Class

    import oracle.xml.xsql.XSQLRequest;
    import java.util.Hashtable;
    import java.io.PrintWriter;
    import java.net.URL;
    public class XSQLRequestSample {
      public static void main( String[] args) throws Exception {
         // Construct the URL of the XSQL Page
       URL pageUrl = new URL("file:///C:/foo/bar.xsql");
       // Construct a new XSQL Page request
       XSQLRequest req = new XSQLRequest(pageUrl);
       // Set up a Hashtable of named parameters to pass to the request
       Hashtable params = new Hashtable(3);
       params.put("param1","value1");
       params.put("param2","value2");
       /* If needed, treat an existing, in-memory XMLDocument as if
       ** it were posted to the XSQL Page as part of the request
       req.setPostedDocument(myXMLDocument);
       **
       */
       // Process the page, passing the parameters and writing the output
       // to standard out.
       req.process(params,new PrintWriter(System.out),
                          new PrintWriter(System.err));
      }
    }

See Also: Chapter 12, "Using the XSQL Pages Publishing Framework: Advanced Topics" to learn more about the XSQL Java API

## XSQL Pages Tips and Techniques

This section contains the following topics:

- XSQL Pages Limitations

- Hints for Using the XSQL Servlet

- Resolving Common XSQL Connection Errors

- Security Considerations for XSQL Pages

### XSQL Pages Limitations

HTTP parameters with multibyte names, for example, a parameter whose name is in Kanji, are properly handled when they are inserted into your XSQL page with the <xsql:include-request-params> element. An attempt to refer to a parameter with a multibyte name inside the query statement of an <xsql:query> tag returns an empty string for the parameter value.

As a workaround use a nonmultibyte parameter name. The parameter can still have a multibyte value that can be handled correctly.

### Hints for Using the XSQL Servlet

This section lists the following XSQL Servlet hints:

- Specifying a DTD While Transforming XSQL Output to a WML Document

- Testing Conditions in XSQL Pages

- Passing a Query Result to the WHERE Clause of Another Query

- Handling Multi-Valued HTML Form Parameters

- Invoking PL/SQL Wrapper Procedures to Generate XML Datagrams

- Accessing Contents of Posted XML

- Changing Database Connections Dynamically

- Retrieving the Name of the Current XSQL Page

#### Specifying a DTD While Transforming XSQL Output to a WML Document

You can specify a DTD while transforming XSQL output to a WML document for a wireless application. The technique is to use a built-in facility of the XSLT stylesheet called `<xsl:output>`. The following example illustrates this technique:

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output type="xml" doctype-system="your.dtd"/>
      <xsl:template match="/">
      </xsl:template>
        ...
    </xsl:stylesheet>

The preceding stylesheet produces an XML result that includes the following code, where `"your.dtd"` can be any valid absolute or relative URL.:

`<!DOCTYPE xxxx SYSTEM "your.dtd">`

#### Testing Conditions in XSQL Pages

You can include if-then logic in your XSQL pages. Example 11-17 illustrates a technique for executing a query based on a test of a parameter value.

Example 11-17 Conditional Statements in XSQL Pages

    <xsql:if-param name="security" equals="admin">
      <xsql:query>
          SELECT ....
      </xsql:query>
    </xsq:when>
    <xsql:if-param name="security" equals="user">
      <xsql:query>
          SELECT ....
      </xsql:query>
    </xsql:if-param>

See Also: Chapter 27, "XSQL Pages Reference" to learn about the <xsql:if-param> action

#### Passing a Query Result to the WHERE Clause of Another Query

If you have two queries in an XSQL page, then you can use the value of a select list item of the first query in the second query by using page parameters. Example 11-18 illustrates this technique.

Example 11-18 Passing Values Among SQL Queries

    <page xmlns:xsql="urn:oracle-xsql" connection="demo">
      <!-- Value of page param "xxx" will be first column of first row -->
      <xsql:set-page-param name="xxx">
        SELECT one FROM table1 WHERE ...
      </xsl:set-param-param>
      <xsql:query bind-params="xxx">
        SELECT col3,col4 FROM table2
        WHERE col3 = ?
      </xsql:query>
    </page>

#### Handling Multi-Valued HTML Form Parameters

In some situations you may need to process multi-valued HTML `<form>` parameters that are needed for `<input name="choices" type="checkbox">`. Use the parameter array notation on your parameter name (for example, `choices[]`) to refer to the array of values from the selected check boxes.

Assume that you have a multi-valued parameter named `guy`. You can use the array parameter notation in an XSQL page as shown in Example 11-19.

Example 11-19 Handling Multi-Valued Parameters

    <page xmlns:xsql="urn:oracle-xsql">
      <xsql:set-page-param name="guy-list" value="{@guy[]}"
                           treat-list-as-array="yes"/>
      <xsql:set-page-param name="quoted-guys" value="{@guy[]}"
                           treat-list-as-array="yes" quote-array-values="yes"/>
      <xsql:include-param name="guy-list"/>
      <xsql:include-param name="quoted-guys"/>
      <xsql:include-param name="guy[]"/>
    </page>

Assume that you request this page is requested with the following URL, which contains multiple parameters of the same name to produce a multi-valued attribute:

    http://yourserver.com/page.xsql?guy=Curly&guy=Larry&guy=Moe

The page returned looks like the following:

    <page>
      <guy-list>Curly,Larry,Moe</guy-list>
      <quoted-guys>'Curly','Larry','Moe'</quoted-guys>
      <guy>
        <value>Curly</value>
        <value>Larry</value>
        <value>Moe</value>
      </guy>
    </page>

You can also use the value of a multi-valued page parameter in a SQL statement `WHERE` clause by using the code shown in Example 11-20.

Example 11-20 Using Multi-Valued Page Parameters in a SQL Statement

    <page connection="demo" xmlns:xsql="urn:oracle-xsql">
      <xsql:set-page-param name="quoted-guys" value="{@guy[]}"
                           treat-list-as-array="yes"
                           quote-array-values="yes"/>
      <xsql:query>
        SELECT *
        FROM   sometable
        WHERE  name IN ({@quoted-guys})
      </xsql:query>
    </page>

#### Invoking PL/SQL Wrapper Procedures to Generate XML Datagrams

You cannot set parameter values by binding them in the position of `OUT` variables with <xsql:dml>. Only `IN` parameters are supported for binding. You can create a wrapper procedure, however, that constructs XML elements with the HTTP package. Your XSQL page can then invoke the wrapper procedure with <xsql:include-owa>.

Example 11-21 shows a PL/SQL procedure that accepts two `IN` parameters, multiplies them and puts the value in one `OUT` parameter, then adds them and puts the result in a second `OUT` parameter.

Example 11-21 addmult PL/SQL Procedure

    CREATE OR REPLACE PROCEDURE addmult(arg1        NUMBER, arg2        NUMBER,
                                        sumval  OUT NUMBER, prodval OUT NUMBER)
    IS
    BEGIN
      sumval := arg1 + arg2;
      prodval := arg1 * arg2;
    END;

You can write the PL/SQL procedure in Example 11-22 to wrap the procedure in Example 11-21. The `addmultwrapper` procedure accepts the `IN` arguments that the `addmult` procedure preceding expects, and then encodes the `OUT` values as an XML datagram that you print to the OWA page buffer.

Example 11-22 addmultwrapper PL/SQL Procedure

    CREATE OR REPLACE PROCEDURE addmultwrapper(arg1 NUMBER, arg2 NUMBER)
    IS
      sumval  NUMBER;
      prodval NUMBER;
      xml     VARCHAR2(2000);
    BEGIN
      -- Call the procedure with OUT values
      addmult(arg1,arg2,sumval,prodval);
      -- Then produce XML that encodes the OUT values
      xml := '<addmult>'||
             '<sum>'||sumval||'</sum>'||
             '<product>'||prodval||'</product>'||
             '</addmult>';
      -- Print the XML result to the OWA page buffer for return
      HTP.P(xml);
    END;

The XSQL page shown in Example 11-23 constructs an XML document by including a call to the PL/SQL wrapper procedure.

Example 11-23 addmult.xsql

    <page connection="demo" xmlns:xsql="urn:oracle-xsql">
      <xsql:include-owa bind-params="arg1 arg2">
        BEGIN addmultwrapper(?,?); END;
      </xsql:include-owa>
    </page>

Suppose that you invoke `addmult.xsql` by entering a URL in a browser as follows:

    http://yourserver.com/addmult.xsql?arg1=30&arg2=45

The XML datagram returned by the servlet reflects the `OUT` values as follows:

    <page>
      <addmult><sum>75</sum><product>1350</product></addmult>
    </page>

#### Accessing Contents of Posted XML

The XSQL page processor can access the contents of posted XML. Any XML document can be posted and handled by the feature that XSQL supports.

For example, an XSQL page can access the contents of an inbound SOAP message by using the `xpath=`"`XpathExpression`" attribute in the <xsql:set-page-param> action. Alternatively, custom action handlers can gain direct access to the SOAP message body by calling `getPageRequest().getPostedDocument()`. To create the SOAP response body to return to the client, use an XSLT stylesheet or a custom serializer implementation to write the XML response in an appropriate SOAP-encoded format.

See Also: The Airport SOAP demo for an example of using an XSQL page to implement a SOAP-based Web Service

#### Changing Database Connections Dynamically

Suppose that you want to choose database connections dynamically when invoking an XSQL page. For example, you may want to switch between a test database and a production database.You can achieve this goal by including an XSQL parameter in the `connection` attribute of the XSQL page. Make sure to define an attribute of the same name to serve as the default value for the connection name.

Assume that in your XSQL configuration file you define connections for database `testdb` and `proddb`. You then write an XSQL page with the following <xsql:query> element:

    <xsql:query conn="testdb" connection="{@conn}" xmlns:xsql="urn:oracle-xsql">
      ...
    </xsql:query>

If you request this page without any parameters, then the value of the `conn` parameter is `testdb`, so the page uses the connection named `testdb` defined in the XSQL configuration file. If you request the page with `conn=proddb`, then the page uses the connection named `proddb` instead.

#### Retrieving the Name of the Current XSQL Page

An XSQL page can access its own name in a generic way at runtime in order to construct links to the current page. You can use a helper method like the one shown in Example 11-24 to retrieve the name of the page inside a custom action handler.

Example 11-24 Obtaining the Name of the Current XSQL Page

    private String curPageName(XSQLPageRequest req) {
      String thisPage = req.getSourceDocumentURI();;
      int pos = thisPage.lastIndexOf('/');
      if (pos >=0) thisPage = thisPage.substring(pos+1);
      pos = thisPage.indexOf('?');
      if (pos >=0) thisPage = thisPage.substring(0,pos-1);
      return thisPage;
    }

### Resolving Common XSQL Connection Errors

This section contains tips for responding to XSQL errors:

- Receiving "Unable to Connect" Errors

- Receiving "No Posted Document to Process" When Using HTTP POST

#### Receiving "Unable to Connect" Errors

Suppose that you are unable to connect to a database and errors similar to the following when running the `helloworld.xsql` sample program:

    Oracle XSQL Servlet Page Processor
    XSQL-007: Cannot acquire a database connection to process page.
    Connection refused(DESCRIPTION=(TMP=)(VSNNUM=135286784)(ERR=12505)
    (ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))

The preceding errors indicate that the XSQL servlet is attempting the JDBC connection based on the `<connectiondef>` information for the connection named `demo`, assuming you did not modify the `helloworld.xsql` demo page.

By default the `XSQLConfig.xml` file comes with the entry for the `demo` connection that looks like the following:

    <connection name="demo">
      <username>scott</username>
      <password>tiger</password>
      <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl>
      <driver>oracle.jdbc.driver.OracleDriver</driver>
    </connection>

The error is probably due to one of the following reasons:

- Your database is not on the `localhost` machine.

- Your database `SID` is not `ORCL.`

- Your TNS Listener Port is not `1521`.

#### Receiving "No Posted Document to Process" When Using HTTP POST

When trying to post XML information to an XSQL page for processing, it must be sent by the HTTP `POST` method. This transfer can be effected by an HTML form or an XML document sent by HTTP `POST`. If you try to use HTTP `GET` instead, then there is no posted document, and hence you get the "No posted document to process" error. Use HTTP `POST` instead to cause the correct behavior.

### Security Considerations for XSQL Pages

This section describes best practices for managing security in the XSQL servlet:

- Installing Your XSQL Configuration File in a Safe Directory

- Disabling Default Client Stylesheet Overrides

- Protecting Against the Misuse of Substitution Parameters

#### Installing Your XSQL Configuration File in a Safe Directory

The `XSQLConfig.xml` configuration file contains sensitive database username and password information. This file should not reside in any directory that maps to a virtual path of your Web server, nor in any of its subdirectories. The only required permissions for the configuration file are read permission granted to the UNIX account that owns the servlet engine. Failure to follow this recommendation could mean that a user of your site could browse the contents of your configuration file, thereby obtaining the passwords to database accounts.

#### Disabling Default Client Stylesheet Overrides

By default, the XSQL page processor enables the user to supply a stylesheet in the page request by passing a value for the special `xml-stylesheet` parameter. If you want the stylesheet referenced by the server-side XSQL page to be the only legal stylesheet, then include the `allow-client-style="no"` attribute on the document element of your page. You can also globally change the default setting in the `XSQLConfig.xml` file to disallow client stylesheet overrides. If you take either approach, then the only pages that allow client stylesheet overrides are those that include the `allow-client-style="yes"` attribute on their document element.

#### Protecting Against the Misuse of Substitution Parameters

Any product that supports the use of lexical substitution variables in a SQL query can cause a developer problems. Any time you deploy an XSQL page that allows part of all of a SQL statement to be substituted by a lexical parameter, you must make sure that you have taken appropriate precautions against misuse.

For example, one of the demonstrations that comes with XSQL Pages is the Adhoc Query Demo. It illustrates how you can supply the entire SQL statement of an <xsql:query> action handler as a parameter. This technique is a powerful and beneficial tool when in the right hands, but if you deploy a similar page to your production system, then the user can execute any query that the database security privileges for the connection associated with the page allows. For example, the Adhoc Query Demo is set up to use a connection that maps to the `scott` account, so a user can query any data that `scott` would be allowed to query from SQL*Plus.

You can use the following techniques to make sure your pages are not abused:

- Make sure the database user account associated with the page has only the privileges for reading the tables and views you want your users to see.

- Use true bind variables instead of lexical bind variables when substituting single values in a `SELECT` statement. If you need to parameterize syntactic parts of your SQL statement, then lexical parameters are the only way to proceed. Otherwise, you should use true bind variables so that any attempt to pass an invalid value generates an error instead of producing an unexpected result.